<?php 
class reportProvider{
    
    /*
     * Revenue Pending Report Detail Query
     */
    public static function revenuePendingReportDetailQuery($date_from, $date_to, $conditions){
        /******** Basic Query ********/
        $query = "
            SELECT
                `o`.NAME AS `outlet`,
                `t`.date_receipt AS `date_receipt`,
                SUM(`t`.pax) AS `cover`,
                COUNT(`t`.transaction_id) AS `total_visit`,
                SUM(`t`.food) AS `food`,
                SUM(`t`.beverage) AS `beverage`,
                SUM(`t`.other) AS `other`,
                SUM(`t`.vat) AS `vat`,
                SUM(`t`.sc) AS `sc`,
                SUM(`t`.total) AS `total`
            FROM #__wrd_outlets `o`,
                #__wrd_transaction `t`
            WHERE `t`.CONVERT = 1
                  AND `o`.cid = '01'
                  AND `t`.outlet = `o`.CODE
        ";
        
        
        /******** Add Date Condition ********/
        if(!empty($date_from)){
            $query .= ' AND `t`.date_receipt >= "'.$date_from.'"'; 
        }
        if(!empty($date_to)){
            $query .= ' AND `t`.date_receipt <= "'.$date_to.'" ' ; 
        }
        
        
        /******** Add Group Condition ********/
        $groupBy .= '
            GROUP BY `o`.NAME                
        ';       
        
        /******** Add Sort by Condition ********/
        if($conditions['extra_sort_by'] != '')
        {
            switch($conditions['extra_sort_by'])
            {
                case 'dateasc':
                    $orderBy .= '
                        ORDER BY `t`.date_receipt ASC
                    ';
                    break;
                
                case 'datedesc':
                    $orderBy .= '
                        ORDER BY `t`.date_receipt DESC
                    ';
                    break;
                
                case 'outlet':
                    if(!empty($conditions['outlet']) && $conditions['outlet'][0] != '')
                    {
                        $conditions['outlet'] = implode(',', $conditions['outlet']);
                        $query .= ' AND `o`.CODE IN ('.$conditions['outlet'].') ';
                    }
                    $orderBy .= '
                        ORDER BY `t`.date_receipt ASC
                    ';
                    break;
                
                case 'meal':
                    $orderBy .= '
                        ORDER BY total DESC
                    ';
                    break;
            }
        }
        
        
        /******** Build Full Query ********/
        $query .= $groupBy.' '.$orderBy;
        
        
        /******** Return Query ********/
        
        return $query;
    }
    
    /*
     * Membership Club Query
     */
    public static function membershipClubQuery($month, $year)
    {
        $query = array();
		
        $query['new_join_member'] = "
            SELECT
                COUNT(member_id) AS `new_join_member`
            FROM #__wrd_profile
            WHERE 
				`created_date` BETWEEN '".$year."-".$month."-1 00:00:00' AND '".$year."-".$month."-31 23:59:59'
				or 
				`reapply_date` BETWEEN '".$year."-".$month."-1 00:00:00' AND '".$year."-".$month."-31 23:59:59'
        ";
        
		//Tong so Member tinh den thang-nam
		// OR DATE(`reapply_date`) <= '".$year."-".$month."-31 23:59:59'  AND published= 1
        $query['member_total'] = "
            SELECT
                COUNT(DISTINCT member_id) AS `total_member`
            FROM #__wrd_profile
            WHERE DATE(`created_date`) <= '".$year."-".$month."-31 23:59:59' 
               
        ";
        //Member co di an trong thang
        $query['active_member'] = "
            SELECT COUNT(DISTINCT vip_number) AS `active_member`
            FROM #__wrd_transaction
            WHERE MONTH(`date_receipt`) = '".$month."' AND YEAR(`date_receipt`) = '".$year."'
            GROUP BY vip_number
        ";
        //
        $query['pick_up_member'] = "
            SELECT
            COUNT(DISTINCT member_id) AS `total_member`
            FROM #__wrd_profile
            WHERE DATE( `pick_up_date` ) <= '".$year."-".$month."-31 23:59:59'
            AND pick_up_date != '0000-00-00'
        
		";
        //echo '<pre>';print_r($query);
        return $query;
    }
    
    /*
     * @param type
     * active : active member : has pick up card
     * nonactive : inactive member : has not pick up card
     * newjoin: new join member
     */
    public static function membershipClubDetailQuery($month, $year, $day, $type='active')
    {
        $query = array();
        
        if($type == 'nonpickup')
        {
        	$day['from'] = $year['from']."-".$month['from']."-".$day['from']." 00:00:00";
            $day['to'] = $year['to']."-".$month['to']."-".$day['to']." 23:59:59";
            
            $query['items'] = "
                SELECT
                    f_name       AS `f_name`,
					`t`.status_of_card,`t`.pick_up_at,`t`.prefix,
                    `t`.p_email AS `email`,
                    l_name       AS `l_name`,
                    member_id    AS `member_id`,
                    p_email      AS `p_email`,
                    `job`.NAME   AS `job`,
                    `t`.company_name AS `company_name`,
                    dob          AS `dob`,
                    pick_up_date AS `pick_up_date`,
                    `t`.id AS `profile_id`,
                    phone        AS `phone`,t.address, t.c_addr,
					noutlet.`name` as noutletname,
					vipuser.registerDate,
					outlet.`name` as outletname,
					country.`name` as countryname,
					distric.`name` as districname,
					city.`name` as cittyname,
					cdistric.`name` as c_districname,
					ccity.`name` as c_cittyname
                FROM #__wrd_profile `t`
                    LEFT JOIN #__wrd_job `job`
                      ON `t`.job = `job`.id
					LEFT JOIN #__wrd_country as country ON t.nationality = country.id
					LEFT JOIN #__wrd_distric as distric on distric.id = t.distric
					LEFT JOIN #__users as vipuser ON t.user_id = vipuser.id
					LEFT JOIN #__wrd_city as city on city.id = t.city
					LEFT JOIN #__wrd_outlets as noutlet ON t.non_pick_up_at = noutlet.code
					LEFT JOIN #__wrd_outlets as outlet ON t.pick_up_at = outlet.code
					LEFT JOIN #__wrd_city as ccity on ccity.id = t.c_city
					LEFT JOIN #__wrd_distric as cdistric on cdistric.id = t.c_distric
                WHERE `t`.`non_pick_up_date` BETWEEN '".$day['from']."' AND '".$day['to']."' AND (`t`.status_of_card = 2)
                     
            ";    
            $query['total'] = "
                SELECT
                    count(*)
                FROM #__wrd_profile `t`
                    LEFT JOIN #__wrd_job `job`
                      ON `t`.job = `job`.id
					LEFT JOIN #__wrd_country as country ON t.nationality = country.id 
					LEFT JOIN #__wrd_distric as distric on distric.id = t.distric
					LEFT JOIN #__users as vipuser ON t.user_id = vipuser.id
					LEFT JOIN #__wrd_city as city on city.id = t.city
					LEFT JOIN #__wrd_outlets as noutlet ON t.non_pick_up_at = noutlet.code
					LEFT JOIN #__wrd_outlets as outlet ON t.pick_up_at = outlet.code
					LEFT JOIN #__wrd_city as ccity on ccity.id = t.c_city
					LEFT JOIN #__wrd_distric as cdistric on cdistric.id = t.c_distric
                WHERE `non_pick_up_date` BETWEEN '".$day['from']."' AND '".$day['to']."' AND (`t`.status_of_card = 2)
            ";    
            
             // echo str_replace('#_', 'vip', $query['items']);
        }
        else if($type == 'newjoin')
        {
            $day['from'] = trim($year['from'])."-".trim($month['from'])."-".trim($day['from'])." 00:00:00";
            $day['to'] = trim($year['to'])."-".trim($month['to'])."-".trim($day['to'])." 23:59:59";
            
            $query['items'] = "
                SELECT
                    f_name       AS `f_name`,
                    `t`.id AS `profile_id`,
                    `t`.p_email AS `email`,
					`t`.status_of_card,`t`.pick_up_at,`t`.prefix,
                    l_name       AS `l_name`,
                    member_id    AS `member_id`,
                    p_email      AS `p_email`,
                    `job`.NAME   AS `job`,
                    company_name AS `company_name`,
                    dob          AS `dob`,
                    pick_up_date AS `pick_up_date`,
                    phone        AS `phone`,
            		vipuser.registerDate,
					country.`name` as countryname,
					outlet.`name` as outletname,
					noutlet.`name` as noutletname,
					city.`name` as cittyname,
					distric.`name` as districname, 
            		t.address, t.c_addr,
					cdistric.`name` as c_districname,
					ccity.`name` as c_cittyname
                FROM #__wrd_profile `t`
                    LEFT JOIN #__wrd_job `job` ON `t`.job = `job`.id
					LEFT JOIN #__users as vipuser ON t.user_id = vipuser.id 
					LEFT JOIN #__wrd_country as country ON t.nationality = country.id
					LEFT JOIN #__wrd_outlets as outlet ON t.pick_up_at = outlet.code 
					LEFT JOIN #__wrd_outlets as noutlet ON t.non_pick_up_at = noutlet.code 
					LEFT JOIN #__wrd_city as city on city.id = t.city
					LEFT JOIN #__wrd_distric as distric on distric.id = t.distric
					LEFT JOIN #__wrd_city as ccity on ccity.id = t.c_city
					LEFT JOIN #__wrd_distric as cdistric on cdistric.id = t.c_distric	
            		
                WHERE (`t`.`created_date` BETWEEN '".$day['from']."' AND '".$day['to']."' OR `t`.`reapply_date` 
                		BETWEEN '".$day['from']." ' AND '".$day['to']."') AND (`t`.status_of_card != 3)
                      
            ";
            //echo str_replace('#_', 'vip', $query['items']);
            $query['total'] = "
                SELECT
                    count(*)
                FROM #__wrd_profile `t`
                    LEFT JOIN #__wrd_job `job` ON `t`.job = `job`.id
					LEFT JOIN #__users as vipuser ON t.user_id = vipuser.id 
					LEFT JOIN #__wrd_country as country ON t.nationality = country.id
					LEFT JOIN #__wrd_outlets as outlet ON t.pick_up_at = outlet.code 
					LEFT JOIN #__wrd_outlets as noutlet ON t.non_pick_up_at = noutlet.code 
					LEFT JOIN #__wrd_city as city on city.id = t.city
					LEFT JOIN #__wrd_distric as distric on distric.id = t.distric
					LEFT JOIN #__wrd_city as ccity on ccity.id = t.c_city
					LEFT JOIN #__wrd_distric as cdistric on cdistric.id = t.c_distric
                WHERE (`t`.`created_date` BETWEEN '".$day['from']."' AND '".$day['to']."' OR `t`.`reapply_date` 
                		BETWEEN '".$day['from']." ' AND '".$day['to']."') AND (`t`.status_of_card != 3)
			";   
			//echo $query['total'];
        }
        else if($type == 'active')
        {
            $day['from'] = $year['from']."-".$month['from']."-".$day['from'] .' 00:00:00';
            $day['to'] = $year['to']."-".$month['to']."-".$day['to'].' 23:59:59';
            
            $query['items'] = "
                SELECT
                    `t`.vip_number
                FROM #__wrd_transaction `t`
                WHERE `t`.date_receipt BETWEEN '".$day['from']."' AND '".$day['to']."'   
                GROUP BY `t`.vip_number                     
            ";

            $db = JFactory::getDbo();
            $db->setQuery($query['items']);
            $vip_number = '';
            
            $result = $db->loadObjectList();
            if(!empty($result))
            {
                foreach($result as $item)
                {
                    $vip_number .= $item->vip_number.',';        
                }
                $vip_number = trim($vip_number, ',');
            }
            
            $query['items'] = "
                SELECT
                    f_name       AS `f_name`,
                    `t`.id AS `profile_id`,
                    `t`.p_email AS `email`,
					`t`.status_of_card,`t`.pick_up_at,`t`.prefix,
                    l_name       AS `l_name`,
                    member_id    AS `member_id`,
                    p_email      AS `p_email`,
                    `job`.NAME   AS `job`,
                    company_name AS `company_name`,
                    dob          AS `dob`,
                    pick_up_date AS `pick_up_date`,
                    phone        AS `phone`,
					vipuser.registerDate,
					country.`name` as countryname,
					outlet.`name` as outletname,
					noutlet.`name` as noutletname,
					city.`name` as cittyname,
					distric.`name` as districname,
            		t.address, t.c_addr,
					cdistric.`name` as c_districname,
					ccity.`name` as c_cittyname
					
                FROM #__wrd_profile `t`
                    LEFT JOIN #__wrd_job `job`
                      ON `t`.job = `job`.id
					LEFT JOIN #__wrd_country as country ON t.nationality = country.id
					LEFT JOIN #__users as vipuser ON t.user_id = vipuser.id
					LEFT JOIN #__wrd_outlets as outlet ON t.pick_up_at = outlet.code 
					LEFT JOIN #__wrd_outlets as noutlet ON t.non_pick_up_at = noutlet.code 
					LEFT JOIN #__wrd_city as city on city.id = t.city
					LEFT JOIN #__wrd_distric as distric on distric.id = t.distric
					LEFT JOIN #__wrd_city as ccity on ccity.id = t.c_city
					LEFT JOIN #__wrd_distric as cdistric on cdistric.id = t.c_distric
                WHERE  `t`.member_id IN(".$vip_number.")
            ";
            //WHERE (YEAR(`t`.`pick_up_date`) != '0000' OR YEAR(`t`.`pick_up_date`) != '0') AND swipe_card = 'S' AND `t`.member_id
            //echo str_replace('#_', 'vip', $query['items']);
            $query['total'] = "
                SELECT
                    count(*)
                FROM #__wrd_profile `t`
					LEFT JOIN #__users as vipuser ON t.user_id = vipuser.id
					LEFT JOIN #__wrd_country as country ON t.nationality = country.id
					LEFT JOIN #__wrd_outlets as outlet ON t.pick_up_at = outlet.code 
					LEFT JOIN #__wrd_outlets as noutlet ON t.non_pick_up_at = noutlet.code 
					LEFT JOIN #__wrd_city as city on city.id = t.city
					LEFT JOIN #__wrd_city as ccity on ccity.id = t.c_city
					LEFT JOIN #__wrd_distric as cdistric on cdistric.id = t.c_distric
                WHERE `t`.member_id IN(".$vip_number.")
            ";   
        }
        else if($type == 'nonactive')
        {
            
			//$day['from'] = $year['from']."-".$month['from']."-".$day['from'];
            //$day['to'] = $year['to']."-".$month['to']."-".$day['to'];
            
			$day['from'] = $year['from']."-".$month['from']."-".$day['from'] .' 00:00:00';
            $day['to'] = $year['to']."-".$month['to']."-".$day['to'].' 23:59:59';
			
            $query['items'] = "
                SELECT
                    `t`.vip_number
                FROM #__wrd_transaction `t`
                WHERE `t`.date_receipt BETWEEN '".$day['from']."' AND '".$day['to']."'   
                GROUP BY `t`.vip_number                     
            ";
            
            /******* Get list vip_number *******/
            $db = JFactory::getDbo();
            $db->setQuery($query['items']);
            $vip_number = '';
            $result = $db->loadObjectList();
            if(!empty($result))
            {
                foreach($result as $item)
                {
                    $vip_number .= $item->vip_number.',';        
                }
                $vip_number = trim($vip_number, ',');
            }
            
            $query['items'] = "
                SELECT
                    f_name       AS `f_name`,
                    `t`.id AS `profile_id`,`t`.pick_up_at,`t`.prefix,
                    `t`.p_email AS `email`,
                    l_name       AS `l_name`,
                    member_id    AS `member_id`,
                    p_email      AS `p_email`,
                    `job`.NAME   AS `job`,
                    company_name AS `company_name`,
                    dob          AS `dob`,
                    pick_up_date AS `pick_up_date`,
                    phone        AS `phone`,
            		t.address, t.c_addr,
					vipuser.registerDate,
					noutlet.`name` as noutletname,
					outlet.`name` as outletname,
					country.`name` as countryname,
					distric.`name` as districname,
					city.`name` as cittyname,
					cdistric.`name` as c_districname,
					ccity.`name` as c_cittyname
                FROM #__wrd_profile `t`
                    LEFT JOIN #__wrd_job `job`
                      ON `t`.job = `job`.id
					LEFT JOIN #__users as vipuser ON t.user_id = vipuser.id
					LEFT JOIN #__wrd_country as country ON t.nationality = country.id
					LEFT JOIN #__wrd_outlets as outlet ON t.pick_up_at = outlet.code 
					LEFT JOIN #__wrd_outlets as noutlet ON t.non_pick_up_at = noutlet.code 
					LEFT JOIN #__wrd_city as city on city.id = t.city
					LEFT JOIN #__wrd_distric as distric on distric.id = t.distric
					LEFT JOIN #__wrd_city as ccity on ccity.id = t.c_city
					LEFT JOIN #__wrd_distric as cdistric on cdistric.id = t.c_distric	
                WHERE `t`.member_id NOT IN(".$vip_number.")
            ";
            
            $query['total'] = "
                SELECT
                    count(*)
                FROM #__wrd_profile `t`
                    LEFT JOIN #__wrd_job `job`
                      ON `t`.job = `job`.id
					LEFT JOIN #__users as vipuser ON t.user_id = vipuser.id
					LEFT JOIN #__wrd_country as country ON t.nationality = country.id
					LEFT JOIN #__wrd_outlets as outlet ON t.pick_up_at = outlet.code 
					LEFT JOIN #__wrd_outlets as noutlet ON t.non_pick_up_at = noutlet.code 
					LEFT JOIN #__wrd_city as city on city.id = t.city
					LEFT JOIN #__wrd_distric as distric on distric.id = t.distric
					LEFT JOIN #__wrd_city as ccity on ccity.id = t.c_city
					LEFT JOIN #__wrd_distric as cdistric on cdistric.id = t.c_distric
                WHERE `t`.member_id NOT IN(".$vip_number.")
				
            ";    
            //WHERE (YEAR(`t`.`pick_up_date`) != '0000' OR YEAR(`t`.`pick_up_date`) != '0') AND swipe_card = 'S' AND `t`.member_id NOT IN
        }
        else
        {
            $day['from'] = $year['from']."-".$month['from']."-".$day['from']." 00:00:00";
            $day['to'] = $year['to']."-".$month['to']."-".$day['to']." 23:59:59" ;
            
            $query['items'] = "
                SELECT
                    f_name       AS `f_name`,
                    `t`.id AS `profile_id`,`t`.pick_up_at,`t`.prefix,
                    `t`.p_email AS `email`,
                    l_name       AS `l_name`,
                    member_id    AS `member_id`,
                    p_email      AS `p_email`,
                    `job`.NAME   AS `job`,
                    company_name AS `company_name`,
                    dob          AS `dob`,
                    pick_up_date AS `pick_up_date`,
                    phone        AS `phone`,t.address, t.c_addr,
					noutlet.`name` as noutletname,
					vipuser.registerDate,
					outlet.`name` as outletname,
					country.`name` AS countryname,
					distric.`name` as districname,
					city.`name` as cittyname,
					cdistric.`name` as c_districname,
					ccity.`name` as c_cittyname
                FROM #__wrd_profile `t`
                    LEFT JOIN #__wrd_job `job`
                      ON `t`.job = `job`.id
					LEFT JOIN #__users as vipuser ON t.user_id = vipuser.id
					LEFT JOIN #__wrd_outlets as outlet ON t.pick_up_at = outlet.code 
					LEFT JOIN #__wrd_outlets as noutlet ON t.non_pick_up_at = noutlet.code 
					LEFT JOIN #__wrd_city as city on city.id = t.city
					LEFT JOIN #__wrd_distric as distric on distric.id = t.distric
					LEFT JOIN #__wrd_city as ccity on ccity.id = t.c_city
					LEFT JOIN #__wrd_distric as cdistric on cdistric.id = t.c_distric	
					LEFT JOIN #__wrd_country AS country ON country.id = t.c_country
                WHERE `t`.`pick_up_date` BETWEEN '".$day['from']."' AND '".$day['to']."' AND (`t`.status_of_card = 3)
                     
            ";
            
            $query['total'] = "
                SELECT
                    count(*)
                FROM #__wrd_profile as t
					LEFT JOIN #__users as vipuser ON t.user_id = vipuser.id
					LEFT JOIN #__wrd_outlets as outlet ON t.pick_up_at = outlet.code 
					LEFT JOIN #__wrd_outlets as noutlet ON t.non_pick_up_at = noutlet.code 
					LEFT JOIN #__wrd_city as city on city.id = t.city
					LEFT JOIN #__wrd_distric as distric on distric.id = t.distric
					LEFT JOIN #__wrd_city as ccity on ccity.id = t.c_city
					LEFT JOIN #__wrd_distric as cdistric on cdistric.id = t.c_distric
					LEFT JOIN #__wrd_country AS country ON country.id = t.c_country
                WHERE `pick_up_date` BETWEEN '".$day['from']."' AND '".$day['to']."' AND (t.status_of_card = 3)
                      
            ";    
            
            //echo str_replace('#_', 'vip', $query['total']);
        }
		//echo '<pre>';echo $query['items'];
        return $query;
    }
    
    /*
     * Complain report
     */
    public static function complainReport($conditions, $db, $limit=0)
    {
        $query = array();
        $query['items'] = "
            SELECT
                `t`.*,pro.phone,
                `o`.NAME AS `outlet`,
                `o`.cid AS `l_name`,
                `o`.TYPE AS `f_name`,
                `o`.pid AS `profile_id`
                
            FROM #__wrd_complain `t`
                LEFT JOIN #__wrd_outlets `o`
                  ON `o`.CODE = `t`.o_code
				LEFT JOIN #__wrd_profile pro ON pro.member_id = t.vip_number  
            WHERE 1  AND `o`.cid = '01'
        ";
        
        $query['total'] = "
            SELECT
                count(*)
            FROM #__wrd_complain `t`
                LEFT JOIN #__wrd_outlets `o`
                  ON `o`.CODE = `t`.o_code
				LEFT JOIN #__wrd_profile pro ON pro.member_id = t.vip_number  
            WHERE 1  AND `o`.cid = '01'
        ";
        
        $extraQuery = '';
        /************* DATE *************/
        if($conditions['dateFrom'] != '' && $conditions['dateTo'] != '')
        {
            $extraQuery .= " AND `t`.created_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
        }
        
        /************* Filter Field *************/
        if($conditions['filter_field'] != '' && $conditions['search_value'] != '')
        {
            if(intval($conditions['filter_field']) == 1)
            {
                $search = $db->getEscaped( strval($conditions['search_value']), true );
                $search = explode(' ', $search);
                $guestName = implode("%') OR (`t`.guest_name LIKE '%", $search);
                $extraQuery = " AND (`t`.guest_name LIKE '%".$guestName."%') ";
            }
            else
            {
                $extraQuery .= " AND `t`.vip_number = '".strval($conditions['search_value'])."' "; 
            }
        }
        else if($conditions['filter_field'] == '' && $conditions['search_value'] != '')
        {
            $search = $db->getEscaped( strval($conditions['search_value']), true );
            $search = explode(' ', $search);
            $guestName = implode("%') OR (`t`.guest_name LIKE '%", $search);
            $guestName = " (`t`.guest_name LIKE '%".$guestName."%') ";
                
            $extraQuery .= " AND ( ".$guestName." OR `t`.vip_number = '".strval($conditions['search_value'])."' ) "; 
            
        }
        
        /************* COMPLAIN TYPE *************/
        if($conditions['type'] != '')
        {
            $extraQuery .= " AND `t`.complain_type IN(".strval($conditions['type']).") "; 
        }
        
        /************* MEAL PERIOD *************/
        if($conditions['meal'] != '')
        {
            $extraQuery .= " AND `t`.meal_period IN(".strval($conditions['meal']).") "; 
        }
        
        /************* OUTLET *************/
        if($conditions['outlet'] != '')
        {
            $extraQuery .= " AND `t`.o_code IN(".strval($conditions['outlet']).") "; 
        }
        
        /************* HANDLE BY *************/
        if($conditions['handle_by'] != '')
        {
            $extraQuery .= " AND `t`.handle_by IN(".strval($conditions['handle_by']).") "; 
        }
        
        $query['total'] .= $extraQuery;      
        $query['items'] .= $extraQuery.' ORDER BY `t`.id DESC ';    
        //var_dump($conditions);
        //echo $query['items'];
        /************** QUERY DATA **************/ 
        $data = array();
        
        /**** Excel ****/
        if($limit > 0){
            $db->setQuery($query['items'], 0, $limit);
        }
        else if($limit == 0){
			$db->setQuery($query['items']);
		}
        /**** Back end ****/
        if($conditions['limitend'] > 0) {
            $db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
        
        $data['items'] = $db->loadObjectList();
       
        /********** Get user **********/
        if(!empty($data['items']))
        {
            $handByIds = '';
            $userIds = '';            
            $size = count($data['items']);
            
            for($i=0; $i<$size; $i++)
            {
                $handByIds .= $data['items'][$i]->handle_by.',';
                $userIds .= $data['items'][$i]->vip_number.',';
            }
            $handByIds = trim($handByIds, ',');
            $userIds = trim($userIds, ',');
            
            /***** Handle by *****/
            $handleModels = "
                SELECT `t`.NAME as `name`, `t`.id
                FROM #__users `t`
                WHERE `t`.id IN (".$handByIds.")
            ";
            $db->setQuery($handleModels);
            $handleModels = $db->loadObjectList();
            
			if(!empty($handleModels))
            {
                $handleData = array();
                foreach($handleModels as $item)
                {
                    $handleData[$item->id] = $item->name;
                }
            }
            //
            /***** Users *****/
            $userModels = "
                SELECT
                    `t`.l_name,
                    `t`.f_name,
                    `t`.id,
                    `t`.member_id
                FROM #__wrd_profile `t`
                WHERE `t`.member_id IN(".$userIds.")
            ";
            $db->setQuery($userModels);
            $userModels = $db->loadObjectList();
            
            if(!empty($userModels))
            {
                $userData = array();
                foreach($userModels as $item)
                {
                    $userData[$item->member_id] = array('l_name'=>$item->l_name, 'f_name'=>$item->f_name, 'profile_id'=>$item->id);
                }
            }
            
            /************ Assign Data ************/
            for($i=0; $i<$size; $i++)
            {   //var_dump($handleData[$data['items'][$i]->handle_by], $data['items'][$i]->handle_by); die;
                $data['items'][$i]->handle_by = $handleData[$data['items'][$i]->handle_by];
                $data['items'][$i]->l_name = $userData[$data['items'][$i]->vip_number]['l_name'];
                $data['items'][$i]->f_name = $userData[$data['items'][$i]->vip_number]['f_name'];
                $data['items'][$i]->profile_id = $userData[$data['items'][$i]->vip_number]['profile_id'];
            }
            
        }
        
        /************ RETURN DATA ************/
        $db->setQuery($query['total']);
        $data['total'] = $db->loadResult();
        return $data;
    }            
     /*
     * Member birthday
     */
    public static function memberbirthday($conditions, $db, $limit=0)
    {
        //echo '<pre>';print_r($conditions);
		//if($conditions['topDisplaySelect'] != '') $conditions['limitend'] = $conditions['topDisplaySelect'];
		$query = array();
        $query['items'] = "
            SELECT p.prefix,p.member_id,CONCAT(`p`.l_name, ' ', `p`.f_name) AS `full_name`,
			p.p_email,p.phone,p.dob,`d`.name as district,`dd`.name as c_district,`c`.name as city,`cc`.name as c_city,			
            `country`.name as nationality, `ccountry`.name as c_nationality
			FROM #__wrd_profile AS `p`
            LEFT JOIN #__wrd_distric as `d` ON `d`.id = `p`.distric
			LEFT JOIN #__wrd_distric as `dd` ON `dd`.id = `p`.c_distric
			LEFT JOIN #__wrd_city as `c` ON `c`.id = `p`.city
			LEFT JOIN #__wrd_city as `cc` ON `cc`.id = `p`.c_city
			LEFT JOIN #__wrd_country as `country` ON `country`.id = `p`.nationality
			LEFT JOIN #__wrd_country as `ccountry` ON `ccountry`.id = `p`.c_country
			WHERE 1 AND `p`.published = '1'
        ";
		
        $query['total'] = "
            SELECT
                count(*)
           FROM #__wrd_profile AS `p`
           WHERE 1 AND `p`.published = '1'
        ";
        $extraQuery = '';
        if($conditions['dateFrom'] != '' && $conditions['dateTo'] != '')
        {
            // echo "<pre>";print_r($conditions);
			 $from = explode("-",$conditions['dateFrom']);
			 $to = explode("-",$conditions['dateTo']);
				
			if( ($to[1] - $from[1]) >= 2 ){
				$extraQuery .= " AND ( MONTH( `dob` ) BETWEEN '".($from[1] + 1) ."' AND  '".($to[1] - 1)."' ) 
								 OR ( ( DAYOFMONTH( dob ) BETWEEN '".$from[0] ."' AND 31) AND (MONTH( dob ) = '".$from[1]."') )
								 OR ( ( DAYOFMONTH( dob ) BETWEEN 1 AND '".$to[0] ."') 	AND (MONTH( dob ) = '".$to[1]."') )
				";
			}
		
			
			else if( ($to[1] - $from[1]) == 1 ){
				$extraQuery .= " AND ( ( DAYOFMONTH( dob ) BETWEEN '".$from[0] ."' AND 31) AND ( MONTH( dob ) = '".$from[1]."' ) )
								 OR ( ( DAYOFMONTH( dob ) BETWEEN 1 AND '".$to[0] ."') AND ( MONTH( dob ) = '".$to[1]."' ) )	
				";
			}
			
			else if( ($to[1] - $from[1]) == 0 ){
				
				if( $from[0] < $to[0] ){
					
					$extraQuery .= " AND ( DAYOFMONTH( dob ) BETWEEN '".$from[0] ."' AND '".$to[0] ."') AND ( MONTH( dob ) = '".$from[1]."' )";
				}
				else {
				
					$extraQuery .= " AND ( DAYOFMONTH( dob ) BETWEEN '".$to[0] ."' AND '".$from[0] ."') AND ( MONTH( dob ) = '".$from[1]."' )";
				}
				
			}
			else if( $to[1] < $from[1] ){
				
				if( $from[1] < 11 ){
					$extraQuery .= " AND ( ( (( DAYOFMONTH( dob ) BETWEEN '".$from[0] ."' AND 31 ) AND ( MONTH( dob ) = '".$from[1]."' )) 
											OR ( MONTH( dob ) BETWEEN '".($from[1]+1)."' AND 12 ) )
											
					";
				}
				
				if( $from[1] == 11 ) {
					
					$extraQuery .= " AND ( ( (( DAYOFMONTH( dob ) BETWEEN '".$from[0] ."' AND 31 ) AND ( MONTH( dob ) = '".$from[1]."' )) 
											 )
											
					";					
				}
				if( $from[1] == 12 ) {
					
					$extraQuery .= " AND ( ( (( DAYOFMONTH( dob ) BETWEEN '".$from[0] ."' AND 31 ) AND ( MONTH( dob ) = '".$from[1]."' )) 
											 )
											
					";					
				}
				if( $to[1] >1 ){
					
					$extraQuery .= " OR ( (( DAYOFMONTH( dob ) BETWEEN 1 AND '".$to[0] ."' ) AND ( MONTH( dob ) = '".$to[1]."' )) 
											OR ( MONTH( dob ) BETWEEN 1 AND '".($to[1]-1) ."' ) )
								)			
					";
					
				}
				
				if( $to[1] == 1){
					
					$extraQuery .= " OR  (( DAYOFMONTH( dob ) BETWEEN 1 AND '".$to[0] ."' ) AND ( MONTH( dob ) = '".$to[1]."' )) 
											 )
						";	
				}
				
			} //$to[1] < $from[1]
			
			 
        }   //$conditions['dateFrom'] != '' && $conditions['dateTo'] != ''   
		
        $query['total'] .= $extraQuery;      
        $query['items'] .= $extraQuery." ORDER BY MONTH( dob ) ASC, DAY( dob ) ASC ";         
        /************** QUERY DATA **************/ 
        $data = array();
        /**** Excel ****/
        $db->setQuery($query['items']);
		
		if($limit > 0){
            $db->setQuery($query['items'], 0, $limit);
        }
		else if($limit == 0){
			$db->setQuery($query['items']);
		}
        if($conditions['limitend'] > 0) {
            
			$db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		
		$data['items'] = $db->loadObjectList();
        $db->setQuery($query['total']);
        $data['total'] = $db->loadResult();
        return $data;
    } 
    /*
     * Redemption report
     */
    public static function redemptionreport($conditions, $db, $limit=0)
    {
        $query = array();
        $query['items'] = "
            SELECT
                    `p`.NAME     AS `product`,
                CONCAT(`pro`.l_name,' ', `pro`.f_name) AS `full_name`,
                `pro`.id     AS `profile_id`,
                `pro`.phone     AS `phone`,
                `pro`.member_id,
                `o`.NAME     AS `outlet`,
                `t`.*
            FROM #__wrd_redemption `t`
                LEFT JOIN #__wrd_products `p`
                  ON `t`.product_id = `p`.id
                LEFT JOIN #__wrd_profile `pro`
                  ON `t`.user_id = `pro`.user_id
                LEFT JOIN #__wrd_outlets `o`
                  ON `t`.pick_up_at = `o`.CODE
            WHERE 1 AND `o`.cid = '01'
        ";
        
        $query['total'] = "
            SELECT
                count(*)
            FROM #__wrd_redemption `t`
                LEFT JOIN #__wrd_products `p`
                  ON `t`.product_id = `p`.id
                LEFT JOIN #__wrd_profile `pro`
                  ON `t`.user_id = `pro`.user_id
                LEFT JOIN #__wrd_outlets `o`
                  ON `t`.pick_up_at = `o`.CODE
            WHERE 1  AND `o`.cid = '01'
        ";
        
        $extraQuery = '';
        /************* DATE *************/
        if($conditions['dateFrom'] != '' && $conditions['dateTo'] != '')
        {
            switch($conditions['status'])
            {
                case 1:
                    $extraQuery .= " AND `t`.approved_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
                    break;
                
                case 2:
                    $extraQuery .= " AND `t`.status_of_redemption_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
                    break;
                
                case 3:
                    $extraQuery .= " AND `t`.pick_up_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
                    break;
                    
                default:
                    $extraQuery .= " AND `t`.exchange_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
                    break;        
            }
        }
		
        /************* STATUS  *************/
        switch($conditions['status'])
        {
            case 1: // Approved
                $extraQuery .= " AND approved = 1 "; 
                break;
            
            
            case 2: // Deliver to Outlet
                $extraQuery .= " AND status_of_redemption = 1 "; 
                break;
            
            
            case 3: // Pick Up
                $extraQuery .= " AND YEAR(`t`.pick_up_date) > 0 AND `t`.pick_up_at != '' "; 
                break;
        }        
        
        
        /************* PRODUCT *************/
        if($conditions['products'] != '')
        {
            $extraQuery .= " AND `t`.product_id IN(".strval($conditions['products']).") "; 
        }
        
        
        /************* OUTLET *************/
        if($conditions['outlet'] != '')
        {
            $extraQuery .= " AND `t`.pick_up_at IN(".strval($conditions['outlet']).") "; 
        }
        
            
        /************* Filter Field *************/
        if($conditions['filter_field'] != '' && $conditions['search_value'] != '')
        {
            if(intval($conditions['filter_field']) == 1)
            {
                $extraQuery .= " 
                    GROUP BY `t`.id
                    HAVING `full_name` LIKE '%".strval($conditions['search_value'])."%'
                "; 
            }
            else
            {
                $extraQuery .= " AND `pro`.member_id = '".strval($conditions['search_value'])."' "; 
            }
        }
        else if($conditions['filter_field'] == '' && $conditions['search_value'] != '')
        {
            $extraQuery .= " 
                GROUP BY `t`.id
                HAVING (`full_name` LIKE '%".strval($conditions['search_value'])."%') OR (`pro`.member_id = '".strval($conditions['search_value'])."')
            "; 
        }
        
        
        $query['total'] .= $extraQuery;      
        $query['items'] .= $extraQuery.' ORDER BY `t`.id DESC ';    
       
        /************** QUERY DATA **************/ 
        $data = array();
       // echo $query['items'];die;
        /**** Excel ****/
        if($limit > 0){
            $db->setQuery($query['items'], 0, $limit);
        }
        else if($limit == 0){
			$db->setQuery($query['items']);
		}
        /**** Back end ****/
        if($conditions['limitend'] > 0) {
            $db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		$db = $db->setQuery($query['items']);
		$data['items'] = $db->loadObjectList();
		
        /************ RETURN DATA ************/
        $db->setQuery($query['total']);
        $data['total'] = $db->loadResult();
        return $data;
    }  
	/*
     * Redemption report
     */
    public static function redemptionreportnew($conditions, $db, $limit=0)
    {
        $query = array();
        $query['items'] = "
            SELECT DISTINCT
                 `p`.NAME   AS `product`,`p`.price,`p`.cost_pricing,
                `pro`.member_id,
                `t`.exchange_date,`t`.approved_date,`t`.code,`t`.point,`t`.quantity
            FROM #__wrd_redemption `t`
                LEFT JOIN #__wrd_products `p`
                  ON `t`.product_id = `p`.id
                LEFT JOIN #__wrd_profile `pro`
                  ON `t`.user_id = `pro`.user_id
                LEFT JOIN #__wrd_outlets `o`
                  ON `t`.pick_up_at = `o`.CODE
            WHERE `p`.voucher = 0 AND `o`.cid = '01'
        ";
        $query['total'] = "
            SELECT
                count(*)
            FROM #__wrd_redemption `t`
                LEFT JOIN #__wrd_products `p`
                  ON `t`.product_id = `p`.id
                LEFT JOIN #__wrd_profile `pro`
                  ON `t`.user_id = `pro`.user_id
                LEFT JOIN #__wrd_outlets `o`
                  ON `t`.pick_up_at = `o`.CODE
            WHERE 1  AND `o`.cid = '01'
        ";
        
        $extraQuery = '';
        /************* DATE *************/
        if($conditions['dateFrom'] != '' && $conditions['dateTo'] != '')
        {
            switch($conditions['status'])
            {
                case 1:
                    $extraQuery .= " AND `t`.approved_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
                    break;
                
                case 2:
                    $extraQuery .= " AND `t`.status_of_redemption_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
                    break;
                
                case 3:
                    $extraQuery .= " AND `t`.pick_up_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
                    break;
                    
                default:
                    $extraQuery .= " AND `t`.exchange_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
                    break;        
            }
        }
        /************* STATUS  *************/
        switch($conditions['status'])
        {
            case 1: // Approved
                $extraQuery .= " AND approved = 1 "; 
                break;
            
            
            case 2: // Deliver to Outlet
                $extraQuery .= " AND status_of_redemption = 1 "; 
                break;
            
            
            case 3: // Pick Up
                $extraQuery .= " AND YEAR(`t`.pick_up_date) > 0 AND `t`.pick_up_at != '' "; 
                break;
        }        
        
        
        /************* PRODUCT *************/
        if($conditions['products'] != '')
        {
            $extraQuery .= " AND `t`.product_id IN(".strval($conditions['products']).") "; 
        }
        
        
        /************* OUTLET *************/
        if($conditions['outlet'] != '')
        {
            $extraQuery .= " AND `t`.pick_up_at IN(".strval($conditions['outlet']).") "; 
        }
        
            
        /************* Filter Field *************/
        if($conditions['filter_field'] != '' && $conditions['search_value'] != '')
        {
            if(intval($conditions['filter_field']) == 1)
            {
                $extraQuery .= " 
                    GROUP BY `t`.id
                    HAVING `full_name` LIKE '%".strval($conditions['search_value'])."%'
                "; 
            }
            else
            {
                $extraQuery .= " AND `pro`.member_id = '".strval($conditions['search_value'])."' "; 
            }
        }
        else if($conditions['filter_field'] == '' && $conditions['search_value'] != '')
        {
            $extraQuery .= " 
                GROUP BY `t`.id
                HAVING (`full_name` LIKE '%".strval($conditions['search_value'])."%') OR (`pro`.member_id = '".strval($conditions['search_value'])."')
            "; 
        }
        
        
        $query['total'] .= $extraQuery;      
        $query['items'] .= $extraQuery.' ORDER BY `t`.id DESC ';          
        /************** QUERY DATA **************/ 
        $data = array();
	   
	   $db->setQuery($query['items']);
	   if($limit > 0){
            $db->setQuery($query['items'], 0, $limit);
        }
		else if($limit == 0){
			$db->setQuery($query['items']);
		}
        /**** Back end ****/
        if($conditions['limitend'] > 0) {
            $db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		$data['items'] = $db->loadObjectList();
        /************ RETURN DATA ************/
        $db->setQuery($query['total']);
        $data['total'] = $db->loadResult();
        return $data;
    } 
	
	public static function transactionreport($conditions, $db, $limit=0)
    {
		
		$query = array();
        $query['items'] = "
            SELECT t.*,pro.id AS profile_id,pro.f_name,pro.l_name,o.name as outletName
            FROM #__wrd_transaction `t`
                LEFT JOIN #__wrd_profile `pro`
                  ON `t`.vip_number = `pro`.member_id
				LEFT JOIN #__wrd_outlets o ON o.code = t.outlet
				
            WHERE 1 
        ";
		
        $query['total'] = "
            SELECT
                count(*)
            FROM #__wrd_transaction `t`
                LEFT JOIN #__wrd_profile `pro`
                  ON `t`.vip_number = `pro`.member_id
				LEFT JOIN #__wrd_outlets o ON o.code = t.outlet
            WHERE 1
        ";
        
        $extraQuery = '';
        /************* DATE *************/
        //if($conditions['dateFrom'] != '' && $conditions['dateTo'] != '')
       // {
           
		   $extraQuery .= " AND `t`.date_receipt BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
        //}
        /************* OUTLET *************/
        if($conditions['outlet'] != '')
        {
            $extraQuery .= " AND `t`.outlet IN(".strval($conditions['outlet']).") "; 
        }
        $query['total'] .= $extraQuery;      
        $query['items'] .= $extraQuery.' ORDER BY `t`.id DESC ';          
        /************** QUERY DATA **************/ 
        $data = array();
	   
	   $db->setQuery($query['items']);
	   if($limit > 0){
            $db->setQuery($query['items'], 0, $limit);
        }
		else if($limit == 0){
			$db->setQuery($query['items']);
		}
        /**** Back end ****/
        if($conditions['limitend'] > 0) {
            $db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		$data['items'] = $db->loadObjectList();
        /************ RETURN DATA ************/
        $db->setQuery($query['total']);
        $data['total'] = $db->loadResult();
		//echo $query["items"];
		//echo "<pre>"; print_r($conditions);
		//echo "<pre>";print_r($data);exit;
	   return $data;
    } 	
	public static function transactionreportweb($conditions, $db, $limit=0)
    {
		
		$query = array();
        $query['items'] = "
            SELECT t.*,pro.id AS profile_id,pro.f_name,pro.l_name,o.name as outletName,`p`.point
            FROM #__wrd_transaction `t`
                LEFT JOIN #__wrd_profile `pro`
                  ON `t`.vip_number = `pro`.member_id
				LEFT JOIN #__wrd_outlets o ON o.code = t.outlet
				INNER JOIN #__wrd_point `p` ON `p`.transaction = `t`.transaction_id	
            WHERE 1 
        ";
		
        $query['total'] = "
            SELECT
                count(*)
            FROM #__wrd_transaction `t`
                LEFT JOIN #__wrd_profile `pro`
                  ON `t`.vip_number = `pro`.member_id
				LEFT JOIN #__wrd_outlets o ON o.code = t.outlet
				INNER JOIN #__wrd_point `p` ON `p`.transaction = `t`.transaction_id
            WHERE 1
        ";
        
        $extraQuery = '';
        /************* DATE *************/
        //if($conditions['dateFrom'] != '' && $conditions['dateTo'] != '')
       // {
           
		   $extraQuery .= " AND `t`.date_receipt BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
        //}
        /************* OUTLET *************/
        if($conditions['outlet'] != '')
        {
            $extraQuery .= " AND `t`.outlet IN(".strval($conditions['outlet']).") "; 
        }
        $query['total'] .= $extraQuery;      
        $query['items'] .= $extraQuery.' ORDER BY `t`.id DESC ';          
        /************** QUERY DATA **************/ 
        $data = array();
	   
	   $db->setQuery($query['items']);
	   if($limit > 0){
            $db->setQuery($query['items'], 0, $limit);
        }
		else if($limit == 0){
			$db->setQuery($query['items']);
		}
        /**** Back end ****/
        if($conditions['limitend'] > 0) {
            $db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		$data['items'] = $db->loadObjectList();
        /************ RETURN DATA ************/
        $db->setQuery($query['total']);
        $data['total'] = $db->loadResult();
		//echo $query["items"];
		//echo "<pre>"; print_r($conditions);
		//echo "<pre>";print_r($data);exit;
	   return $data;
    } 	
	public static function redemptionreportproduct($conditions, $db, $limit=0)
    {
        $query = array();
        $query['items'] = "
            SELECT DISTINCT
                 `p`.NAME   AS `product`,`p`.price,`p`.cost_pricing,
                `pro`.member_id,`pro`.f_name,`pro`.l_name,
                `t`.exchange_date,`t`.approved_date,`t`.code,`t`.point,`t`.cash,`t`.quantity,`t`.point
            FROM #__wrd_redemption `t`
                LEFT JOIN #__wrd_products `p`
                  ON `t`.product_id = `p`.id
                LEFT JOIN #__wrd_profile `pro`
                  ON `t`.user_id = `pro`.user_id
                LEFT JOIN #__wrd_outlets `o`
                  ON `t`.pick_up_at = `o`.CODE
            WHERE `p`.voucher = 0  AND `o`.cid = '01'
        ";
		
		$query['total'] = "
            SELECT count(*)
            FROM #__wrd_redemption `t`
                LEFT JOIN #__wrd_products `p`
                  ON `t`.product_id = `p`.id
                LEFT JOIN #__wrd_profile `pro`
                  ON `t`.user_id = `pro`.user_id
                LEFT JOIN #__wrd_outlets `o`
                  ON `t`.pick_up_at = `o`.CODE
            WHERE `p`.voucher = 0  AND `o`.cid = '01'
        ";
        $extraQuery = '';
        
        /************* STATUS  *************/
		switch($conditions['status'])
        {
            case 0: // Approved
                $extraQuery .= " AND approved = 0 "; 
                break;
            case 1: 
                $extraQuery .= " AND approved = 1 "; 
                break;
			case -1: 
                $extraQuery .= " AND (approved = 1 || approved = 0) "; 
                break;	
            
        }        
        /************* PRODUCT *************/
        if($conditions['products'] != '')
        {
            $extraQuery .= " AND `t`.product_id IN(".strval($conditions['products']).") "; 
        }
        
        
        /************* OUTLET *************/
        if($conditions['outlet'] != '')
        {
            $extraQuery .= " AND `t`.pick_up_at IN(".strval($conditions['outlet']).") "; 
        }
        if($conditions['dateFrom'] != '' && $conditions['dateTo'] != '')
        {
            $extraQuery .= " AND `t`.exchange_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
        }
        $query['total'] .= $extraQuery;      
        $query['items'] .= $extraQuery.' ORDER BY `t`.id DESC ';          
        /************** QUERY DATA **************/ 
        $data = array();
	  // echo '<pre>';print_r($conditions);exit;
	  // echo $query['items'];
	   $db->setQuery($query['items']);
	   if($limit > 0){
            $db->setQuery($query['items'], 0, $limit);
        }
		else if($limit == 0){
			$db->setQuery($query['items']);
		}
        /**** Back end ****/
        if($conditions['limitend'] > 0) {
            $db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		$data['items'] = $db->loadObjectList();
        /************ RETURN DATA ************/
		$db->setQuery($query['total']);
        $data['total'] = $db->loadResult();
        return $data;
    }  
    /*
     * Redemption report voucher
     */
    public static function redemptionreportvoucher($conditions, $db, $limit=0)
    {
		//echo "<pre>";print_r($conditions);
		$query = array();
        $query['items'] = "
            SELECT DISTINCT v.id,v.outlet_voucher,v.outlet_name_use_voucher,v.member_id,v.product_id,v.product_cost,
			v.cost_pricing,v.date_voucher,v.code_redemption,v.code_voucher,v.expired,v.status,
			p.name AS productName,o.*,pro.id as profile_id,pro.f_name,pro.l_name,r.point,r.cash,r.exchange_date
            FROM #__wrd_redem_report_voucher AS v
			RIGHT JOIN #__wrd_rrv_outlet AS o ON v.id = o.rrvid
            INNER JOIN #__wrd_products AS p ON v.product_id = p.id
			INNER JOIN #__wrd_profile AS pro ON pro.member_id = v.member_id
			INNER JOIN #__wrd_redemption AS r ON r.idrv = v.id
			WHERE 1 
        ";
        
        $query['total'] = "
            SELECT
                count(*)
            FROM #__wrd_redem_report_voucher AS v
			RIGHT JOIN #__wrd_rrv_outlet AS o ON v.id = o.rrvid
            INNER JOIN #__wrd_products AS p ON v.product_id = p.id
			INNER JOIN #__wrd_profile AS pro ON pro.member_id = v.member_id
 			INNER JOIN #__wrd_redemption AS r ON r.idrv = v.id
			WHERE 1 
        ";
        $extraQuery = '';
        /************* DATE *************/
        if($conditions['dateFrom'] != '' && $conditions['dateTo'] != '')
        {
            if($conditions['status'] != -1){
				$extraQuery .= " AND `v`.date_voucher BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
			}
        }
		
		if($conditions['dateFromredem'] != '' && $conditions['dateToredem'] != '')
        {
			
			$extraQuery .= " AND r.exchange_date BETWEEN '".strval($conditions['dateFromredem'])."' AND '".strval($conditions['dateToredem'])."' "; 
        }
		//echo $extraQuery;
		/************* STATUS  *************/
        if($conditions['status'] == '') $conditions['status'] = 1;
		if($conditions['status'] != '' && $conditions['status'] != -1)
        {
            $extraQuery .= " AND `v`.status = '".$conditions['status']."'"; 
        }
		/************* OUTLET *************/
        if($conditions['outlet'] != '')
        {
            $extraQuery .= " AND `o`.outlet IN(".strval($conditions['outlet']).") "; 
        } 
		//if($conditions['status'] != -1){
			$query['total'] .= $extraQuery;      
			$query['items'] .= $extraQuery;
		//}
                 
       // echo $query['items'];
		/************** QUERY DATA **************/ 
       $data = array();
	   $query['items'] .= ' ORDER BY `v`.id DESC ';
	   $db->setQuery($query['items']);
	   if($limit > 0){
            $db->setQuery($query['items'], 0, $limit);
        }
		else if($limit == 0){
			$db->setQuery($query['items']);
		}
        /**** Back end ****/
        if($conditions['limitend'] > 0) {
            $db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		//echo $query['items'];
		$data['items'] = $db->loadObjectList();
        /************ RETURN DATA ************/
        $db->setQuery($query['total']);
		$data['total'] = $db->loadResult();
		//echo "<pre>";print_r($data);exit;
		return $data;
		
    } 
	 public static function memberstatisticjob($conditions, $db, $limit=0)
    {
		//echo "<h1> mai lam tiep </h1>";
		//echo 333;exit;
		//echo "<pre>";print_r($conditions);
		$query = array();
        $query['items'] = "
            SELECT pro.id as profile_id,pro.member_id,pro.hobbies,pro.f_name,pro.l_name,pro.phone,pro.o_job,pro.p_email,pro.company_name,
            i.name as industry,d.name as department,j.name as job_position
			FROM #__wrd_profile AS pro
				LEFT JOIN #__wrd_industry AS `i` ON `pro`.industry = `i`.id
				LEFT JOIN #__wrd_department AS `d` ON `pro`.department = `d`.id
				LEFT JOIN #__wrd_job AS `j` ON `j`.id = `pro`.job
			WHERE 1 
        ";
        
        $query['total'] = "
            SELECT
                count(*)
            FROM #__wrd_profile AS pro
				LEFT JOIN #__wrd_industry AS `i` ON `pro`.industry = `i`.id
				LEFT JOIN #__wrd_department AS `d` ON `pro`.department = `d`.id
				LEFT JOIN #__wrd_job AS `j` ON `j`.id = `pro`.job
			WHERE 1
        ";
        $extraQuery = '';

        if($conditions['listIndustry'] != '')
        {
			$extraQuery .= " AND `pro`.industry IN (".strval($conditions['listIndustry']).") "; 
        }
		if($conditions['department'] != '')
        {
			$extraQuery .= " AND `pro`.department IN (".strval($conditions['department']).") "; 
        }
		if($conditions['listJob'] != '')
        {
			$extraQuery .= " AND `pro`.job IN (".strval($conditions['listJob']).") "; 
        }
		
       $data = array();
	   
	   $query['items'] .= $extraQuery;
	   $query['total'] .= $extraQuery;
	   
	   $query['items'] .= ' ORDER BY profile_id DESC ';
	   //echo $query['items'];
	   $db->setQuery($query['items']);
	   if($limit > 0){
            $db->setQuery($query['items'], 0, $limit);
        }
		else if($limit == 0){
			$db->setQuery($query['items']);
		}
        /**** Back end ****/
        if($conditions['limitend'] > 0) {
            $db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		//echo $query['items'];
		$data['items'] = $db->loadObjectList();
        /************ RETURN DATA ************/
        $db->setQuery($query['total']);
		$data['total'] = $db->loadResult();
		//echo "<pre>";print_r($data);exit;
		return $data;
    
	} 
	public static function memberreport($conditions, $db, $limit=0)
    {
		$query = array();
        $query['items'] = "
            SELECT pro.id as profile_id,pro.member_id,pro.hobbies,pro.f_name,pro.l_name,
			pro.prefix,pro.dob,pro.phone,pro.referer_phone,pro.p_email AS email, pro.alternative_email,pro.address,pro.p_o_u,
			pro.company_name,d.name as distric,c.name as city,pro.o_job,pro.c_addr,`cd`.name as c_distric,
			`cc`.name AS c_city,`co`.name as nationality,pro.p_o_u,pro.created_date,pro.non_pick_up_date,
			pro.pick_up_date,`outlet`.name as pick_up_at,`noutlet`.name as non_pick_up_at,
			pro.reapply_date
			FROM #__wrd_profile AS pro
				LEFT JOIN #__wrd_distric AS `d` ON `pro`.distric = `d`.id
				LEFT JOIN #__wrd_distric AS `cd` ON `pro`.c_distric = `cd`.id
				LEFT JOIN #__wrd_city AS `c` ON `pro`.city = `c`.id
				LEFT JOIN #__wrd_city AS `cc` ON `pro`.c_city = `cc`.id
				LEFT JOIN #__wrd_country AS `co` ON `pro`.nationality = `co`.id
				LEFT JOIN #__wrd_outlets AS `outlet` ON `pro`.pick_up_at = `outlet`.code
				LEFT JOIN #__wrd_outlets AS `noutlet` ON `pro`.non_pick_up_at = `noutlet`.code
			WHERE 1 
        ";
        
        $query['total'] = "
            SELECT
                count(*)
            FROM #__wrd_profile AS pro
				LEFT JOIN #__wrd_distric AS `d` ON `pro`.distric = `d`.id
				LEFT JOIN #__wrd_distric AS `cd` ON `pro`.c_distric = `cd`.id
				LEFT JOIN #__wrd_city AS `c` ON `pro`.city = `c`.id
				LEFT JOIN #__wrd_city AS `cc` ON `pro`.c_city = `cc`.id
				LEFT JOIN #__wrd_country AS `co` ON `pro`.country = `co`.id
				LEFT JOIN #__wrd_outlets AS `outlet` ON `pro`.pick_up_at = `outlet`.code
				LEFT JOIN #__wrd_outlets AS `noutlet` ON `pro`.non_pick_up_at = `noutlet`.code
			WHERE 1 
        ";
        $extraQuery = '';
		
		if($limit != -1){
		
			$extraQuery .= " AND (`pro`.created_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
			
			$extraQuery .= " OR `pro`.`reapply_date` BETWEEN '".strval($conditions['dateFrom'])." ' AND '".strval($conditions['dateTo'])."' )";
		}
       $data = array();
	   
	   $query['items'] .= $extraQuery;
	   $query['total'] .= $extraQuery;
	   
	   $query['items'] .= ' ORDER BY profile_id DESC ';
	   //echo $query['items'];
	   $db->setQuery($query['items']);
	   if($limit > 0){
            $db->setQuery($query['items'], 0, $limit);
        }
		else if($limit == 0){
			$db->setQuery($query['items']);
		}
        /**** Back end ****/
        if($conditions['limitend'] > 0) {
            $db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		//echo $query['items'];exit;
		$data['items'] = $db->loadObjectList();
        /************ RETURN DATA ************/
        $db->setQuery($query['total']);
		$data['total'] = $db->loadResult();
		//echo "<pre>";print_r($data);exit;
		return $data;
    
	}
	public static function getTotal($db,$vip_number,$outlet,$dateFrom,$dateTo,$baseon = 1){
		
		if($baseon == 1){
		
			$sql = "SELECT sum(total) as total FROM #__wrd_transaction 
					WHERE vip_number='".$vip_number."' AND outlet= '".$outlet."' 
							AND (date_receipt BETWEEN '".$dateFrom."' AND '".$dateTo."') ";
		}
		else if($baseon == 2){
			$sql = "SELECT sum(netsale) as total 
					FROM #__wrd_transaction WHERE vip_number='".$vip_number."' 
							AND outlet= '".$outlet."' AND (date_receipt BETWEEN '".$dateFrom."' AND '".$dateTo."') ";
		}
		else if($baseon == 3){
		
			$sql = "SELECT count(*) as total FROM #__wrd_transaction 
					WHERE vip_number='".$vip_number."' AND outlet= '".$outlet."' 
							AND (date_receipt BETWEEN '".$dateFrom."' AND '".$dateTo."') ";
		}
		else {
			return null;
		}
		
		$db->setQuery($sql);
		$total = $db->loadObject(); 
		return $total->total;
		
	}
		
	public static function outletreport($conditions, $db, $limit=0)
    {
		$query = array();
        $query['items'] = "
            SELECT DISTINCT vip_number
			FROM #__wrd_transaction AS t
			WHERE 1  
        ";
        
        $query['total'] = "
            SELECT
                count(DISTINCT vip_number)
            FROM #__wrd_transaction AS t
			WHERE 1
        ";
       
	   $extraQuery = '';
	   $extraQuery .= " AND `t`.date_receipt BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
        
       $data = array();
	   
	   $query['items'] .= $extraQuery;
	   $query['total'] .= $extraQuery;
	   $query['items'] .= ' ORDER BY `t`.id DESC';
	   
	   //echo $query['items'];
	   $db->setQuery($query['items']);
	   if($limit > 0){
            $db->setQuery($query['items'], 0, $limit);
        }
		else if($limit == 0){
			$db->setQuery($query['items']);
		}
        /**** Back end ****/
        if($conditions['limitend'] > 0) {
            $db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		//echo $query['items'];
		$data['items'] = $db->loadObjectList();
        /************ RETURN DATA ************/
        $db->setQuery($query['total']);
		$data['total'] = $db->loadResult();
		//echo "<pre>";print_r($data);exit;
		return $data;
    
	}
    public static function redempreport($conditions,$db, $limit=0)
    {
		$query = array();
		$query['items'] = "
            SELECT DISTINCT
                 `p`.NAME   AS `productName`,`p`.price AS product_cost,`p`.cost_pricing,
                `t`.exchange_date,`t`.approved,`t`.approved_date,`t`.code,`t`.point,`t`.cash,`t`.quantity,t.code AS code_redemption,`t`.code_voucher,
				`t`.expired,
				`v`.id,`v`.outlet_voucher,`v`.outlet_name_use_voucher,`v`.product_id,
			     `v`.date_voucher,`v`.status,
				 `pro`.member_id,`pro`.f_name,`pro`.l_name
            FROM #__wrd_redemption `t`
                LEFT JOIN #__wrd_products `p`
                  ON `t`.product_id = `p`.id
				LEFT JOIN #__wrd_redem_report_voucher v 
				  ON `t`.idrv = `v`.id
				LEFT JOIN #__wrd_profile pro
				  ON `t`.user_id = `pro`.user_id 	
            WHERE 1 
        ";
		
        $query['total'] = "
            SELECT 
                count(*)
            FROM #__wrd_redemption `t`
                LEFT JOIN #__wrd_products `p`
                  ON `t`.product_id = `p`.id
				LEFT JOIN #__wrd_redem_report_voucher v 
				  ON `t`.idrv = `v`.id
				LEFT JOIN #__wrd_profile pro
				  ON `t`.user_id = `pro`.user_id 	
            WHERE 1 
        ";
        
        
		/************** QUERY DATA **************/ 
        $data = array();
	
	   $extraQuery = "";
	   if($conditions['status'] != -1){
	   
			$extraQuery .= " AND `t`.approved = ".$conditions['status'];
	   }
	   if($conditions['dateFrom'] != '' && $conditions['dateTo'] != '')
	   {
	   
		$extraQuery .= " AND `t`.exchange_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
	   
	   }
	   
	   
	   $query['items'].= $extraQuery;
	   
	   $query['total'].= $extraQuery;
	   
	   $query['items'] .= " ORDER BY `t`.id DESC";
	    
	   //echo  $query['items'];
	   
	   $db->setQuery($query['items']);
	   
	   if($limit > 0){
	   
            $db->setQuery($query['items'], 0, $limit);
        }
		else if($limit == 0){
		
			$db->setQuery($query['items']);
		}
        if($conditions['limitend'] > 0) {
		
            $db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		$data['items'] = $db->loadObjectList();
        $db->setQuery($query['total']);
		$data['total'] = $db->loadResult();	
		//echo "<pre>";print_r($data);exit;
		return $data;
    } 
	public static function redempreportdetail($conditions,$db, $limit=0)
		{
			$query = array();
			$query['items'] = "
				SELECT DISTINCT
					 `p`.NAME   AS `productName`,`p`.price AS product_cost,`p`.cost_pricing,
					`t`.exchange_date,`t`.approved_date,`t`.code,`t`.point,`t`.cash,`t`.quantity,t.code AS code_redemption,`t`.code_voucher,
					`t`.expired,
					`v`.id,`v`.outlet_voucher,`v`.outlet_name_use_voucher,`v`.product_id,
					 `v`.date_voucher,`v`.status,`o`.*,
					 `pro`.member_id,`pro`.f_name,`pro`.l_name
				FROM #__wrd_redemption `t`
					LEFT JOIN #__wrd_products `p`
					  ON `t`.product_id = `p`.id
					LEFT JOIN #__wrd_redem_report_voucher v 
					  ON `t`.idrv = `v`.id
					LEFT JOIN #__wrd_rrv_outlet o
					  ON `v`.id = `o`.rrvid 
					LEFT JOIN #__wrd_profile pro
					  ON `t`.user_id = `pro`.user_id 	
				WHERE 1 
			";
			
			$query['total'] = "
				SELECT 
					count(*)
				FROM #__wrd_redemption `t`
					LEFT JOIN #__wrd_products `p`
					  ON `t`.product_id = `p`.id
					LEFT JOIN #__wrd_redem_report_voucher v 
					  ON `t`.idrv = `v`.id
					LEFT JOIN #__wrd_rrv_outlet o
					  ON `v`.id = `o`.rrvid 
					LEFT JOIN #__wrd_profile pro
					  ON `t`.user_id = `pro`.user_id 	
				WHERE 1 
			";
			
			$query['items']. " ORDER BY `t`.id DESC";
			/************** QUERY DATA **************/ 
			$data = array();
		  //echo  $query['items'];
		   $db->setQuery($query['items']);
		   if($limit > 0){
				$db->setQuery($query['items'], 0, $limit);
			}
			else if($limit == 0){
				$db->setQuery($query['items']);
			}
			if($conditions['limitend'] > 0) {
				$db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
			}
			$data['items'] = $db->loadObjectList();
			$db->setQuery($query['total']);
			$data['total'] = $db->loadResult();	
			//echo "<pre>";print_r($data);exit;
			return $data;
		} 	
    /*
     * Member Service report
     */
    public static function memberServiceReport($conditions, $db, $limit=0)
    {
        $query = array();
        $query['items'] = "
            SELECT
                `t`.*,pro.phone,
                `o`.NAME AS `outlet`,
                `o`.cid AS `l_name`,
                `o`.TYPE AS `f_name`,
                `o`.address as `transaction`,
                `o`.pid AS `profile_id`,
                `o`.address AS `pick_up_date`
            FROM #__wrd_mservices `t`
                LEFT JOIN #__wrd_outlets `o`
                  ON `o`.CODE = `t`.o_code
			    LEFT JOIN #__wrd_profile pro ON pro.member_id = t.vip_number 		
            WHERE 1 AND `o`.cid = '01'
        ";
        
        $query['total'] = "
            SELECT
                count(*)
            FROM #__wrd_mservices `t`
                 LEFT JOIN #__wrd_outlets `o`
                  ON `o`.CODE = `t`.o_code 
				 LEFT JOIN #__wrd_profile pro ON pro.member_id = t.vip_number 
            WHERE 1 AND `o`.cid = '01'
        ";
        
        $extraQuery = '';
        /************* DATE *************/
        if($conditions['dateFrom'] != '' && $conditions['dateTo'] != '')
        {
            $extraQuery .= " AND `t`.created_date BETWEEN '".strval($conditions['dateFrom'])."' AND '".strval($conditions['dateTo'])."' "; 
        }
        
        /************* Filter Field *************/
        if($conditions['filter_field'] != '' && $conditions['search_value'] != '')
        {
            if(intval($conditions['filter_field']) == 1)
            {
                $search = $db->getEscaped( strval($conditions['search_value']), true );
                $search = explode(' ', $search);
                $guestName = implode("%') OR (`t`.guest_name LIKE '%", $search);
                $extraQuery = " AND (`t`.guest_name LIKE '%".$guestName."%') ";
            }
            else
            {
                $extraQuery .= " AND `t`.vip_number = '".strval($conditions['search_value'])."' "; 
            }
        }
        else if($conditions['filter_field'] == '' && $conditions['search_value'] != '')
        {
            $search = $db->getEscaped( strval($conditions['search_value']), true );
            $search = explode(' ', $search);
            $guestName = implode("%') OR (`t`.guest_name LIKE '%", $search);
            $guestName = " (`t`.guest_name LIKE '%".$guestName."%') ";
                
            $extraQuery .= " AND ( ".$guestName." OR `t`.vip_number = '".strval($conditions['search_value'])."' ) "; 
        }
        
        /************* COMPLAIN TYPE *************/
        if($conditions['type'] != '')
        {
            //$extraQuery .= " AND `t`.complain_type IN(".strval($conditions['type']).") "; 
        }
        
        /************* MEAL PERIOD *************/
        if($conditions['meal'] != '')
        {
            $extraQuery .= " AND `t`.meal_period IN(".strval($conditions['meal']).") "; 
        }
        
        /************* MEMBER TYPE *************/
        if($conditions['member_type'] != '')
        {
            $extraQuery .= " AND `t`.member_type IN(".strval($conditions['member_type']).") "; 
        }
        
        /************* OUTLET *************/
        if($conditions['outlet'] != '')
        {
            $extraQuery .= " AND `t`.o_code IN(".strval($conditions['outlet']).") "; 
        }
        
        /************* HANDLE BY *************/
        if($conditions['handle_by'] != '')
        {
            $extraQuery .= " AND `t`.handle_by IN(".strval($conditions['handle_by']).") "; 
        }
        
        $query['total'] .= $extraQuery;      
        $query['items'] .= $extraQuery.' ORDER BY `t`.created_date DESC ';    

        /************** QUERY DATA **************/ 
        $data = array();
        
        /**** Excel ****/
        
		if($limit > 0){
			$db->setQuery($query['items'], 0, $limit);
        }
		else if($limit == 0){
			$db->setQuery($query['items']);
		}
		
        if($conditions['limitend'] > 0) {
			$db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }

        $data['items'] = $db->loadObjectList();
        
        /********** Get user **********/
        if(!empty($data['items']))
        {
            $handByIds = '';
            $userIds = '';            
            $size = count($data['items']);
            
            for($i=0; $i<$size; $i++)
            {
                $handByIds .= $data['items'][$i]->handle_by.',';
                $userIds .= $data['items'][$i]->vip_number.',';
            }
            $handByIds = trim($handByIds, ',');
            $userIds = trim($userIds, ',');
            
            /***** Handle by *****/
            $handleModels = "
                SELECT `t`.NAME as `name`, `t`.id
                FROM #__users `t`
                WHERE `t`.id IN (".$handByIds.")
            ";
            $db->setQuery($handleModels);
            $handleModels = $db->loadObjectList();
            if(!empty($handleModels))
            {
                $handleData = array();
                foreach($handleModels as $item)
                {
                    $handleData[$item->id] = $item->name;
                }
            }
            
            /***** Users *****/
            $userModels = "
                SELECT
                    `t`.l_name,
                    `t`.f_name,
                    `t`.id,
                    `t`.pick_up_date,
                    `t`.member_id
                FROM #__wrd_profile `t`
                WHERE `t`.member_id IN(".$userIds.")
            ";
            $db->setQuery($userModels);
            $userModels = $db->loadObjectList();
            
            if(!empty($userModels))
            {
                $userData = array();
                foreach($userModels as $item)
                {
                    $userData[$item->member_id] = array('l_name'=>$item->l_name, 'f_name'=>$item->f_name, 'profile_id'=>$item->id, 'pick_up_date'=>$item->pick_up_date);
                }
            }
            
            /***** Transaction *****/
            $sql = "
                SELECT
                    MAX(`t`.date_receipt) AS `date_receipt`,
                    `t`.vip_number
                FROM #__wrd_transaction `t`
                WHERE `t`.vip_number IN(".$userIds.")
                GROUP BY `t`.vip_number;
            ";
            $db->setQuery($sql);
            $transactions = $db->loadObjectList();
            if(!empty($transactions))
            {
                $transactionData = array();
                foreach($transactions as $item)
                {
                    $transactionData[$item->vip_number] = $item->date_receipt;
                }
            }
            
            /************ Assign Data ************/
            for($i=0; $i<$size; $i++)
            {   //var_dump($handleData[$data['items'][$i]->handle_by], $data['items'][$i]->handle_by); die;
                $data['items'][$i]->handle_by = $handleData[$data['items'][$i]->handle_by];
                $data['items'][$i]->l_name = $userData[$data['items'][$i]->vip_number]['l_name'];
                $data['items'][$i]->f_name = $userData[$data['items'][$i]->vip_number]['f_name'];
                $data['items'][$i]->profile_id = $userData[$data['items'][$i]->vip_number]['profile_id'];
                $data['items'][$i]->pick_up_date = $userData[$data['items'][$i]->vip_number]['pick_up_date'];
                $data['items'][$i]->transaction = $transactionData[$data['items'][$i]->vip_number];
                
                /*** Member type ***/
                if($data['items'][$i]->member_type != ''){
                    $data['items'][$i]->member_type = explode('_', $data['items'][$i]->member_type);
                    if($data['items'][$i]->member_type[0] > 0):
                        $data['items'][$i]->member_type[0] = 'Top Spenders'; 
                    else:
                        unset($data['items'][$i]->member_type[0]);
                    endif;
                    
                    if($data['items'][$i]->member_type[1] > 0):
                        $data['items'][$i]->member_type[1] = 'New Join Members'; 
                    else:
                        unset($data['items'][$i]->member_type[1]);
                    endif;
                    
                    if($data['items'][$i]->member_type[2] > 0):
                        $data['items'][$i]->member_type[2] = 'Inactive Members'; 
                    else:
                        unset($data['items'][$i]->member_type[2]);
                    endif;
                    
					if($data['items'][$i]->member_type[3] > 0):
                        $data['items'][$i]->member_type[3] = 'Daily Top Spenders'; 
                    else:
                        unset($data['items'][$i]->member_type[3]);
                    endif;
					
					
					
                    $data['items'][$i]->member_type = implode(', ', $data['items'][$i]->member_type);;
                }
            }
            
        }
        
        /************ RETURN DATA ************/
        $db->setQuery($query['total']);
        $data['total'] = $db->loadResult();
        return $data;
    }  
    
    /*
     * Member Statistic
     */
    public static function memberStatistic($conditions)
    {
        
		/********* Check Conditions *********/    
        $extraQuery = "";
		if($conditions['date_from'] != '' && $conditions['date_to'] != '')
        {
			
			$extraQuery .= " WHERE 1 AND (`t`.`created_date` BETWEEN '".$conditions['date_from']."' AND '".$conditions['date_to']."' OR `t`.`reapply_date` 
                		BETWEEN '".$conditions['date_from']." ' AND '".$conditions['date_to']."')";
		}
		
		$query = array();
        $query['gender'] = " 
            SELECT
                COUNT(DISTINCT `t`.member_id)   AS `count`,
                `t`.gender AS `gender`
            FROM #__wrd_profile `t`
				$extraQuery
            GROUP BY `t`.gender
        ";     
        $query['age'] = " 
            SELECT
                COUNT(DISTINCT `t`.member_id) AS `count`,
                YEAR(`t`.dob) AS `dob`
            FROM #__wrd_profile `t`
				$extraQuery
            GROUP BY YEAR(`t`.dob)
        ";
        
        $query['job'] = "
            SELECT
                COUNT(DISTINCT `t`.member_id) AS `count`,
                `t`.job AS `job`
            FROM #__wrd_profile `t`
				$extraQuery
			GROUP BY `t`.job
        ";
        
        $query['location'] = " 
            SELECT
                COUNT(DISTINCT `t`.member_id) AS `count`,
                `t`.distric AS `distric`,
                `dis`.NAME AS `name`
            FROM #__wrd_profile `t`
                INNER JOIN #__wrd_distric `dis` ON `t`.distric = `dis`.id
				$extraQuery
            GROUP BY `t`.distric
        ";
		
        if($conditions['statistic_by'] == 'gender')
        {
            $query = $query['gender'];
        }
        else if($conditions['statistic_by'] == 'age')
        {
            $query = $query['age'];
        }
        else if($conditions['statistic_by'] == 'job')
        {
            $query = $query['job'];
        }
        else if($conditions['statistic_by'] == 'location')
        {
            $query = $query['location'];
        }
        else
        {
            //echo "<pre>";print_r($query);
			return $query;
        }
        /********* Build Query *********/
		return $query;    
    }
    
    /*
     * Member Statistic Filter
     */
    public static function memberStatisticFilter($conditions)
    {
        //echo '<pre>';print_r($conditions);
		/********* Check Conditions *********/    
         $query['items'] = "
              SELECT
                `dis`.name      AS `distric`,
                `job`.NAME       AS `job`,
                `t`.gender       AS `gender`,
                `t`.l_name       AS `l_name`,
                `t`.f_name       AS `f_name`,
                `t`.company_name AS `company`,
                `t`.p_email      AS `email`,
                `t`.phone        AS `phone`,
                `t`.member_id    AS `member_id`,
                `t`.created_date AS `created_date`,
				`t`.reapply_date,
                `t`.id           AS `id`,
                (`t`.dob)       AS `dob`
            FROM #__wrd_profile `t`
                LEFT JOIN #__wrd_distric `dis`
                  ON `t`.distric = `dis`.id
                LEFT JOIN #__wrd_job `job`
                  ON `job`.id = `t`.job
			WHERE 1 ";
         
        $query['total'] = "
            SELECT
                count(*)
            FROM #__wrd_profile `t`
			LEFT JOIN #__wrd_distric `dis`
			  ON `t`.distric = `dis`.id
		    LEFT JOIN #__wrd_job `job`
			  ON `t`.job = `job`.id           
            WHERE 1 
        ";
        //var_dump($conditions['sex']).'<br/>';
        $extraQuery = '';

		if($conditions['date_from'] != '' && $conditions['date_to'] != '')
        {
			
			$extraQuery .= " AND (`t`.`created_date` BETWEEN '".$conditions['date_from']."' AND '".$conditions['date_to']."' OR `t`.`reapply_date` 
                		BETWEEN '".$conditions['date_from']." ' AND '".$conditions['date_to']."')";
		}
		
		if($conditions['sex'] != '' && $conditions['sex'] != "''")
        {
            $extraQuery .= " AND `t`.gender IN (".$conditions['sex'].") ";
        }
        
        /************* AGE *************/
        if($conditions['age'] != '')
        {
            $conditions['age'] = explode(',', $conditions['age']);
            $extraQuery .= " AND ( ";       
            for($i=0; $i<count($conditions['age']); $i++)
            {
                $conditions['age'][$i] = explode('-', $conditions['age'][$i]);   
                if($conditions['age'][$i][0] == 'unknown') {
                    if($i == 0){
                        $extraQuery .= " (YEAR(`t`.dob) < '".(date('Y')-100)."' OR  YEAR(`t`.dob) = '0' OR YEAR(`t`.dob) >= '".(date('Y')-17)."') ";
                    }
                    else {
                        $extraQuery .= " OR (YEAR(`t`.dob) < '".(date('Y')-100)."' OR  YEAR(`t`.dob) = '0' OR YEAR(`t`.dob) >= '".(date('Y')-17)."') ";
                        //echo $extraQuery; die;
                    }
                }
                else {
                    if($i == 0){
                        $extraQuery .= " YEAR(`t`.dob) BETWEEN '".$conditions['age'][$i][0]."' AND '".$conditions['age'][$i][1]."' ";           
                    }
                    else {
                        $extraQuery .= " OR YEAR(`t`.dob) BETWEEN '".$conditions['age'][$i][0]."' AND '".$conditions['age'][$i][1]."' ";       
                    }
                }
            }
            $extraQuery .= " ) ";       
            
        }
        
        /************* JOB *************/
        if($conditions['job'] != '')
        {
            $extraQuery .= " AND `t`.job IN(".$conditions['job'].") ";
        }
        
        /************* LOCATION *************/
        if($conditions['location'] != '')
        {
            $extraQuery .= " AND `t`.distric IN(".$conditions['location'].") ";
        }
        
        $extraQuery .= " GROUP BY `t`.member_id  ";
        $query['total'] .= $extraQuery;      
        $query['items'] .= $extraQuery.' ORDER BY `l_name` ';      
        //echo $query['items']; 
        //die;
        /********* Build Query *********/
       //echo "<pre>";print_r($query);
        return $query;    
    }
    
    /*
     * Member Statistic Html View
     */
    public static function memberStatisticHtml($conditions, $items)
    {
        if($conditions == 'gender')
        {
            echo '<table width="100%" class="adminlist"><thead><tr><th style="text-align:left; color:#C00;" colspan="'.count($items).'">Statistic - by Sex</th></tr></thead><tbody>';
                        
                foreach($items as $key => $item):                           
                    echo '<tr class="row'.($key%2).'"><td style="width:10%;" nowrap="nowrap"><strong>'.ucfirst($item->gender).'</strong></td><td nowrap="nowrap">'.number_format($item->count, 0, '.', '.').'</td></tr>';
                endforeach;
            
            echo '</tbody></table>';
        }
        
        else if($conditions == 'location')
        {
            echo '<table width="100%" class="adminlist"><thead><tr><th style="text-align:left; color:#C00;" colspan="'.count($items).'">Statistic - by Location</th></tr></thead><tbody>';
            $districtsName = array('D1', 'D2', 'D3', 'D4', 'D5', 'D6', 'D7', 'D8', 'D9', 'D10', 'D11', 'D12', 'Binh Chanh', 'Binh Tan', 'Binh Thanh', 'Go Vap', 'Tan Binh', 'Tan Phu', 'Phu Nhuan', 'Thu Duc', 'Out of HCMC');
            $districts = array();
            foreach($districtsName as $item):
                $districts[] = array('key'=>$item, 'value'=>0);
            endforeach;
            $mapping = array(
                '5'=>'0',   '6'=>'1',   '7'=>'2',   '8'=>'3',   '9'=>'4',   '10'=>'5',   '11'=>'6',   '12'=>'7',   '13'=>'8',   '14'=>'9',
                '15'=>'10',   '16'=>'11',   '26'=>'12',   '23'=>'13',   '20'=>'14',   '17'=>'15',   '18'=>'16',   '19'=>'17',   '21'=>'18',   
                '22'=>'19'      
            );
            foreach($items as $item):         
                
                if($mapping[$item->distric] != ''):
                    $districts[$mapping[$item->distric]]['value'] += $item->count; 
                    $districts[$mapping[$item->distric]]['key'] = $item->name;
                else:
                    $districts[20]['value'] += $item->count; 
                    $districts[20]['key'] = 'Out of HCMC';
                endif; 
                
            endforeach;
            
            /******** Bind Data ********/
            foreach($districts as $key => $item):
                echo '<tr class="row'.($key%2).'"><td style="width:10%;" nowrap="nowrap"><strong>'.$item['key'].'</strong></td><td nowrap="nowrap">'.number_format($item['value'], 0, '.', '.').'</td></tr>';
            endforeach;
        
        echo '</tbody></table>';
        }
        
        else if($conditions == 'age')
        {
            echo '<table width="100%" class="adminlist"><thead><tr><th style="text-align:left; color:#C00;" colspan="'.count($items).'">Statistic - by Age Group</th></tr></thead><tbody>';
            $age = array(
                array('key'=>'Age 18-25', 'value'=>0),
                array('key'=>'Age 26-35', 'value'=>0),
                array('key'=>'Age 36-45', 'value'=>0),
                array('key'=>'Age 46+', 'value'=>0),
                array('key'=>'Unknown', 'value'=>0)
            );
            $currentYear = date('Y');
            
            foreach($items as $key => $item):  
                if(($currentYear-18) >= $item->dob && $item->dob >= ($currentYear-25) ):
                    $age[0]['value'] += $item->count;                    
                    
                elseif(($currentYear-26) >= $item->dob && $item->dob >= ($currentYear-35) ):
                    $age[1]['value'] += $item->count;
                    
                elseif(($currentYear-36) >= $item->dob && $item->dob >= ($currentYear-45) ):
                    $age[2]['value'] += $item->count;
                
                elseif(($currentYear-46) >= $item->dob && $item->dob >= ($currentYear-100) ):   
                    $age[3]['value'] += $item->count;
                    
                else:
                    $age[4]['value'] += $item->count;
                    
                endif;                                                         
            endforeach;
            
            foreach($age as $key => $item):
                echo '<tr class="row'.($key%2).'"><td style="width:10%;" nowrap="nowrap"><strong>'.ucfirst($item['key']).'</strong></td><td nowrap="nowrap">'.number_format($item['value'], 0, '.', '.').'</td></tr>';
            endforeach;
        
            echo '</tbody></table>';
        }
        
        else if($conditions == 'job')
        {
            echo '<table width="100%" class="adminlist"><thead><tr><th style="text-align:left; color:#C00;" colspan="'.count($items).'">Statistic - by Job Title/Position</th></tr></thead><tbody>';
            $job = array();
            $job[] = array('key'=>'CEO/Chairman', 'value'=>0);
            $job[] = array('key'=>'Director', 'value'=>0);
            $job[] = array('key'=>'Mid Management & Executive Level', 'value'=>0);
            $job[] = array('key'=>'Other', 'value'=>0);
            foreach($items as $item):         
                              
                if($item->job == 1):
                    $job[0]['value'] += $item->count; 
                    
                elseif($item->job == 3):
                    $job[1]['value'] += $item->count; 
                    
                elseif($item->job == 4 || $item->job == 5 || $item->job == 7):
                    $job[2]['value'] += $item->count; 
                    
                else:
                    $job[3]['value'] += $item->count; 
                    
                endif;
                
            endforeach;
            
            /******** Bind Data ********/
            foreach($job as $key => $item):
                echo '<tr class="row'.($key%2).'"><td style="width:10%;" nowrap="nowrap"><strong>'.$item['key'].'</strong></td><td nowrap="nowrap">'.number_format($item['value'], 0, '.', '.').'</td></tr>';
            endforeach;
        
            echo '</tbody></table>';
        }
    }
    
    
}
?>